﻿<?php 
$dbscheme = array("
DROP DATABASE IF EXISTS webdb;","
CREATE DATABASE IF NOT EXISTS webdb
 CHARACTER SET utf8
 COLLATE utf8_general_ci;
");
$dbdata = array("		
		DROP TABLE IF EXISTS messages;","
		DROP TABLE IF EXISTS relation_type;","
		DROP TABLE IF EXISTS talkbacks;","
		DROP TABLE IF EXISTS posts;","
		DROP TABLE IF EXISTS groups;","
		DROP TABLE IF EXISTS users;","
		

		
		
		--
		-- Definition for table users
		--
		
		CREATE TABLE users (
		  uid INT(11) NOT NULL AUTO_INCREMENT,
		  handle VARCHAR(255) NOT NULL,
		  `password` VARCHAR(255) NOT NULL DEFAULT '!',
		  first_name VARCHAR(255) NOT NULL,
		  sur_name VARCHAR(255) NOT NULL,
		  birthday DATE NOT NULL,
		  last_updated DATETIME DEFAULT NULL,
		  email VARCHAR(255) DEFAULT NULL,
		  about VARCHAR(255) DEFAULT NULL,
		  PRIMARY KEY (uid),
		  UNIQUE INDEX UK_users_uid (uid)
		)
		ENGINE = INNODB
		AUTO_INCREMENT = 45
		AVG_ROW_LENGTH = 2048
		CHARACTER SET utf8
		COLLATE utf8_general_ci;		
		","
			--
		-- Definition for table groups
		--
		CREATE TABLE groups (
		  gid INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
		  name VARCHAR(255) NOT NULL,
		  description VARCHAR(2000) DEFAULT NULL,
		  visibility TINYINT(1) NOT NULL DEFAULT 1,
		  last_updated DATETIME DEFAULT NULL,
		  friendsgroup INT(11) DEFAULT NULL,
		  PRIMARY KEY (gid),
		  UNIQUE INDEX UK_groups_gid (gid)
		)
		ENGINE = INNODB
		AUTO_INCREMENT = 53
		AVG_ROW_LENGTH = 963
		CHARACTER SET utf8
		COLLATE utf8_general_ci;
		
		","
		CREATE TABLE messages (
		  mid INT(11) NOT NULL AUTO_INCREMENT,
		  sender INT(11) NOT NULL,
		  receiver INT(11) NOT NULL,
		  Created DATETIME DEFAULT NULL,
		  content VARCHAR(1500) DEFAULT NULL,
		  INDEX FK_messages_users_uid (sender),
		  INDEX FK_messages_users_uid2 (receiver),
		  UNIQUE INDEX mid (mid),
		  CONSTRAINT FK_messages_users_uid FOREIGN KEY (sender)
			REFERENCES users(uid) ON DELETE RESTRICT ON UPDATE RESTRICT,
		  CONSTRAINT FK_messages_users_uid2 FOREIGN KEY (receiver)
			REFERENCES users(uid) ON DELETE RESTRICT ON UPDATE RESTRICT
		)
		ENGINE = INNODB
		AUTO_INCREMENT = 34
		AVG_ROW_LENGTH = 496
		CHARACTER SET utf8
		COLLATE utf8_general_ci;
		","
		
		--
		-- Definition for table relation_type
		--
		
		CREATE TABLE relation_type (
		  uid INT(11) NOT NULL,
		  gid INT(11) UNSIGNED ZEROFILL NOT NULL,
		  type VARCHAR(1) DEFAULT NULL,
		  last_updated DATETIME DEFAULT NULL,
		  PRIMARY KEY (gid, uid),
		  INDEX IX_relation_type (gid, uid),
		  INDEX UK_relation_type (uid, gid),
		  INDEX UK_relation_type_gid (gid),
		  INDEX UK_relation_type_type (type),
		  CONSTRAINT FK_relation_type_groups_gid FOREIGN KEY (gid)
			REFERENCES groups(gid) ON DELETE RESTRICT ON UPDATE RESTRICT,
		  CONSTRAINT FK_relation_type_users_uid FOREIGN KEY (uid)
			REFERENCES users(uid) ON DELETE RESTRICT ON UPDATE RESTRICT
		)
		ENGINE = INNODB
		AVG_ROW_LENGTH = 910
		CHARACTER SET utf8
		COLLATE utf8_general_ci
		ROW_FORMAT = REDUNDANT;

		
		","		
		--
		-- Definition for table posts
		--
		CREATE TABLE posts (
		  Created DATETIME NOT NULL,
		  pid INT(11) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
		  uid INT(11) NOT NULL,
		  gid INT(11) UNSIGNED ZEROFILL NOT NULL,
		  topic VARCHAR(255) NOT NULL,
		  content VARCHAR(1500) DEFAULT NULL,
		  visibility TINYINT(1) NOT NULL DEFAULT 1,
		  last_updated DATETIME DEFAULT NULL,
		  PRIMARY KEY (pid),
		  INDEX FK_posts_groups_gid (gid),
		  INDEX FK_posts_users_uid (uid),
		  CONSTRAINT FK_posts_groups_gid FOREIGN KEY (gid)
			REFERENCES groups(gid) ON DELETE RESTRICT ON UPDATE RESTRICT,
		  CONSTRAINT FK_posts_users_uid FOREIGN KEY (uid)
			REFERENCES users(uid) ON DELETE RESTRICT ON UPDATE RESTRICT
		)
		ENGINE = INNODB
		AUTO_INCREMENT = 29
		AVG_ROW_LENGTH = 8192
		CHARACTER SET utf8
		COLLATE utf8_general_ci
		ROW_FORMAT = REDUNDANT;
		
		
		","
		CREATE TABLE talkbacks (
		  id INT(11) NOT NULL AUTO_INCREMENT,
		  uid INT(11) NOT NULL,
		  pid INT(11) UNSIGNED ZEROFILL NOT NULL DEFAULT 00000000000,
		  visibility TINYINT(1) DEFAULT 1,
		  content VARCHAR(1500) NOT NULL,
		  last_updated DATETIME DEFAULT NULL,
		  Created DATETIME NOT NULL,
		  PRIMARY KEY (id),
		  INDEX FK_talkbacks_posts_pid (pid),
		  INDEX FK_talkbacks_users_uid (uid),
		  CONSTRAINT FK_talkbacks_posts_pid FOREIGN KEY (pid)
			REFERENCES posts(pid) ON DELETE RESTRICT ON UPDATE RESTRICT,
		  CONSTRAINT FK_talkbacks_users_uid FOREIGN KEY (uid)
			REFERENCES users(uid) ON DELETE RESTRICT ON UPDATE RESTRICT
		)
		ENGINE = INNODB
		AUTO_INCREMENT = 8
		AVG_ROW_LENGTH = 2340
		CHARACTER SET utf8
		COLLATE utf8_general_ci;
		

	

	
		");

?>